TD 8 : Modélisation

Modélisation

Modélisation EA
Pattes de corbeau
Crowfoot notation
Published

November 15, 2024

Avec solutions

Objectifs

L’objectif de cette séance est construire des modèles Entité-Association sur des problèmes miniatures.

Modélisation Entité-Association (E/A ou E/R)

Exercice (Supermarché)

Question

Produire un schéma E/R qui décrit des informations concernant les produits d’un supermarché.

Chaque produit a un nom et un prix et appartient à une catégorie.

Le supermarché a plusieurs rayons, un rayon étant caractérisé par un étage et un numéro de rangée. On veut maintenir l’emplacement des produits dans les rayons. Les produits d’une même catégorie sont placés dans le même rayon, mais un rayon peut contenir des produits de plusieurs catégories.

Question

Traduire le schéma EA dans le formalisme des pattes de corbeau

Question

Définir le schéma relationnel correspondant en SQL

Exercice (Location de voitures)

Question

Produire un schéma E/R qui décrit des informations concernant des voitures à louer.

Chaque voiture a une plaque d’immatriculation, une couleur et une marque. Le prix de la location dépend de la catégorie, où chaque catégorie est identifiée par un nom.

Solution
ER Voiture Voiture fabriqué-par fabriqué-par Voiture--fabriqué-par 1:1 appartient-à appartient-à Voiture--appartient-à 1:1 Marque Marque Catégorie Catégorie prix_location prix location Catégorie--prix_location name1 nom name1--Catégorie name2 nom name2--Marque immatriculation immatriculation immatriculation--Voiture couleur couleur couleur--Voiture fabriqué-par--Marque 0:n appartient-à--Catégorie 0:n

Pas d’entités faibles car chaque entité a son propre identifiant.

À discuter : pourrait-on utiliser un lien est-un ( ◃ ) pour modéliser le fait qu’un véhicule relève d’une catégorie ?

Question

Modifier ensuite le schéma pour représenter les modèles de voitures.

Un modèle a un nom, une marque et un nombre de sièges.

Toutes les voitures du même modèle doivent appartenir à la même catégorie de prix.

Solution
ER Voiture Voiture relève-de relève-de Voiture--relève-de 1:1 Marque Marque Catégorie Catégorie prix_location prix location Catégorie--prix_location Modèle Modèle fabriqué-par fabriqué-par Modèle--fabriqué-par 1:1 appartient-à appartient-à Modèle--appartient-à 1:1 name1 nom name1--Catégorie name2 nom name2--Marque name3 nom name3--Modèle immatriculation immatriculation immatriculation--Voiture couleur couleur couleur--Voiture nbre_sieges nombre de sièges nbre_sieges--Modèle fabriqué-par--Marque 0:n appartient-à--Catégorie 0:n relève-de--Modèle 0:n
Question

De plus, on veut distinguer les voitures disponibles des voitures en location. Pour les voitures disponibles on représente l’emplacement. Pour les voitures en location on représente la date et la durée de la location, ainsi que le nom du client.

Solution
ER Voiture Voiture relève-de relève-de Voiture--relève-de 1:1 Marque Marque Catégorie Catégorie prix_location prix location Catégorie--prix_location Modèle Modèle fabriqué-par fabriqué-par Modèle--fabriqué-par 1:1 appartient-à appartient-à Modèle--appartient-à 1:1 Disponible Disponible emplacement emplacement Disponible--emplacement estun1 Est Un Disponible--estun1 En location En location nom client nom client En location--nom client date date En location--date durée durée En location--durée estun2 Est Un En location--estun2 name1 nom name1--Catégorie name2 nom name2--Marque name3 nom name3--Modèle immatriculation immatriculation immatriculation--Voiture couleur couleur couleur--Voiture nbre_sieges nombre de sièges nbre_sieges--Modèle fabriqué-par--Marque 0:n appartient-à--Catégorie 0:n relève-de--Modèle 0:n estun1--Voiture estun2--Voiture

On utilise ici les liens Est Un pour décrire le statut des voitures (spécialisation).

Il faudrait ajouter une contrainte d’exclusion totale : une voiture est soit en location, soit disponible.

On pourrait aussi passer par des attributs statut, emplacement et une entité faible Location.

Question

Traduire le schéma EA dans le formalisme des pattes de corbeau

Solution 

erDiagram
    VOITURE
    MODELE 
    CATEGORIE
    MARQUE
    LOCATION
    MODELE ||..o{ VOITURE : "releve de"
    VOITURE {
      string  immatriculation  PK
      string couleur
      boolean disponible 
      string emplacement 
      string nom_modele FK
    }
    MARQUE ||..o{ MODELE : "fabrique par"
    MODELE {
      string nom PK
      integer nombre_de_sieges 
      string nom_marque FK
      string nom_categorie FK
    }
    MARQUE {
      string nom PK
    }
    CATEGORIE ||..o{ MODELE : "appartient a"
    CATEGORIE {
      string nom PK
      numeric prix_location
    }
    LOCATION |o--|| VOITURE : concerne
    LOCATION {
      string date 
      integer immatriculation PK, FK
      string duree
      integer numero_client
    }

Contraintes externes:

  • Dans VOITURE, disponible si et seulement si emplacement est NOT NULL
  • Dans VOITURE et LOCATION, NOT disponible si et seulement si dans LOCATION, il existe une instance qui réfère à l’instance de VOITURE.

Trouver une meilleure modélisation pour la spécialisation DISPONIBLE/EN LOCATION.

Solution avec dbSchema

Schema Avis d’après dbSchema
Question

Définir le schéma relationnel correspondant en SQL

Solution 
CREATE SCHEMA IF NOT EXISTS schema_avis;

CREATE  TABLE schema_avis.categorie ( 
    nom                  text  NOT NULL  ,
    prix_location        numeric    ,
    CONSTRAINT pk_categorie PRIMARY KEY ( nom )
 );

CREATE  TABLE schema_avis.marque ( 
    nom                  text  NOT NULL  ,
    CONSTRAINT pk_marque PRIMARY KEY ( nom )
 );

CREATE  TABLE schema_avis.modele ( 
    nom                  text  NOT NULL  ,
    nombre_sieges        bigint    ,
    nom_marque           text    ,
    name_categorie       text    ,
    CONSTRAINT pk_modele PRIMARY KEY ( nom )
 );
suite 
CREATE  TABLE schema_avis.voiture ( 
    immatriculation      bigint  NOT NULL  ,
    name_modele          text    ,
    couleur              text    ,
    disponible           boolean  NOT NULL  ,
    emplacement          text    ,
    CONSTRAINT pk_voiture PRIMARY KEY ( immatriculation )
 );

CREATE  TABLE schema_avis.location ( 
    immatriculation      bigint  NOT NULL  ,
    "date"               date  NOT NULL  ,
    duree                bigint  NOT NULL  ,
    numero_client        bigint  NOT NULL  ,
    CONSTRAINT pk_location PRIMARY KEY ( immatriculation )
 );
suite 

ALTER TABLE schema_avis.location 
ADD CONSTRAINT 
  fk_location_voiture 
FOREIGN KEY ( immatriculation ) 
REFERENCES schema_avis.voiture( immatriculation );

ALTER TABLE schema_avis.modele 
ADD CONSTRAINT 
  fk_modele_marque 
FOREIGN KEY ( nom_marque ) 
REFERENCES schema_avis.marque( nom ) 
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE schema_avis.modele 
ADD CONSTRAINT 
  fk_modele_categorie 
FOREIGN KEY ( name_categorie ) 
REFERENCES schema_avis.categorie( nom );

ALTER TABLE schema_avis.voiture 
ADD CONSTRAINT 
  fk_voiture_modele 
FOREIGN KEY ( name_modele ) 
REFERENCES schema_avis.modele( nom ) 
ON DELETE CASCADE ON UPDATE CASCADE;

Exercice (Gestion du personnel d’une entreprise)

Dans une entreprise, chaque employé (identifié par un numéro) est attaché à un département de l’entreprise. Il occupe un bureau et participe à un ou plusieurs projets développés par l’entreprise.

De chaque employé, on connait : le nom, le prénom, les emplois qu’il a occupés à différentes dates et les salaires qu’il a perçus dans ces emplois.

Chaque département est identifié par un numéro, a son budget propre et est dirigé par un directeur faisant partie du personnel de l’entreprise.

Chaque bureau est identifié par un numéro, est rattaché à un département et est caractérisé par sa surface en mètres carrés. Il possède un numéro de téléphone associé.

Chaque projet est identifié par un numéro, possède un certain budget et emploie plusieurs personnes appartenant à différents départements. Chaque employé est affecté pour un certain nombre d’heures à un projet.

Question

Donner un modèle entité-association correspondant à la description ci-dessus.

Question

Modifier votre modélisation pour tenir compte de l’évolution dans le temps de la vie de l’entreprise : les projets ont des durées de vie limitées, chaque employé est affecté à un projet (mais aussi un département) pendant une certaines durée, etc

Question

Traduire le schéma EA dans le formalisme des pattes de corbeau

Question

Définir le schéma relationnel correspondant en SQL